package com.example.demo.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.example.demo.entity.MonitorcodeInfo;
import com.example.demo.entity.MonitorobjInfo;
import com.example.demo.service.TestAccessService;
import org.springframework.stereotype.Component;

import java.sql.*;

/**
 * 验证数据库是否可以联通的实现类
 * Created by tanjun on 2021-12-30.
 */
@Component
public class TestDatabaseAccessService extends BaseAccessService implements TestAccessService {

    public static void main(String[] args) {
        TestDatabaseAccessService testservice=new TestDatabaseAccessService();
        testservice.testAccess("4");
    }

    public void testAccess(String monitorObjId){
        //从数据库数据库的连接信息和测试语句
//从数据库获取IP信息，端口信息
        MonitorobjInfo monitorobjInfo=monitorobjInfoMapper.selectByPrimaryKey(monitorObjId);
        if(monitorobjInfo.getMonitorobjCodeId()==null){
            return;
        }
        MonitorcodeInfo monitorcodeInfo=monitorcodeInfoMapper.selectByPrimaryKey(monitorobjInfo.getMonitorobjCodeId());
        Connection conn=null;
        PreparedStatement preparedStatement=null;
        //ResultSet rs;
        try {
            String driverName=getDriverNameByDatabaseType(monitorcodeInfo.getDatabaseType());
            Class.forName(driverName) ;
            String otherinfo=monitorcodeInfo.getOtherinfo();
            JSONObject jsonObject= JSON.parseObject(otherinfo);
            String username=jsonObject.getString("username");
            String password=jsonObject.getString("password");
            conn = DriverManager.getConnection(monitorcodeInfo.getUrl() , username , password ) ;
            //暂时固定允许的数据库的测试sql，避免通过自己输入而造成，错误或者注入风险。
            String testsql=gettestsqlByDatabaseType(monitorcodeInfo.getDatabaseType());
            preparedStatement= conn.prepareStatement(testsql);
            preparedStatement.execute();
            //还可以继续判断结果集，这里只要能成功执行，就认为可以联通了
            //rs= preparedStatement.getResultSet();
            accessTestResultHandler.handlerAccessTestSuccess(monitorObjId,monitorcodeInfo.getMonitorcodeId());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            accessTestResultHandler.handlerAccessTestfailure(monitorObjId,monitorcodeInfo.getMonitorcodeId());
        } catch (SQLException e) {
            e.printStackTrace();
            accessTestResultHandler.handlerAccessTestfailure(monitorObjId,monitorcodeInfo.getMonitorcodeId());
        }finally {
            if(preparedStatement !=null){   // 关闭声明
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn !=null){  // 关闭连接对象
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
    }

    private String gettestsqlByDatabaseType(String databaseType) {
        switch (databaseType.replaceAll(" ","").toLowerCase()){
            case "mysql":
                return "select now()";
            case "oracle":
                return "select sysdate from dual";
            case "postgresql":
                return "select now()";
            case "sqlserver":
                return "Select CONVERT(varchar(100), GETDATE(), 0)";
            case "db2":
                return "select CURRENT DATE from SYSIBM.SYSDUMMY1";
            case "hive":
                return "select unix_timestamp()";
            default:
                throw new RuntimeException("暂不支持的数据库类型");
        }
    }

    private String getDriverNameByDatabaseType(String databaseType) {
        switch (databaseType.replaceAll(" ","").toLowerCase()){
            case "mysql":
                return "com.mysql.jdbc.Driver";
            case "oracle":
                return "oracle.jdbc.driver.OracleDriver";
            case "postgresql":
                return "org.postgresql.Driver";
            case "sqlserver":
                return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            case "db2":
                return "com.ibm.db2.jdbc.app.DB2.Driver";
            case "hive":
                return "org.apache.hive.jdbc.HiveDriver";
            default:
                throw new RuntimeException("暂不支持的数据库类型");
        }
    }
}
